﻿using Dapper;
using Dapper.Contrib.Extensions;
using Gx.Common;
using Gx.Common.Core;
using Gx.Dal.Extensions;
using Gx.Dal.Shared;
using Gx.Model.Entity;
using Gx.Model.Enums;
using Gx.Model.ViewModel;
using System;
using System.Collections.Generic;
using System.Linq;
namespace Gx.Dal.Manage
{
    public class LKWarehouseContractDal : BaseDal<LKWarehouseContract>
    {

        /// <summary>
        /// 名称是否存在
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public bool AnyName(string name)
        {
            using (var conn = new DbContext())
            {
                var total = conn.Connection.QueryFirst<int>("SELECT COUNT(1) FROM LK_WarehouseContract WHERE IsDeleted=0 AND Name=@name", new { name });
                return total > 0;
            }
        }

        /// <summary>
        /// 获取最大编号
        /// </summary>
        /// <returns></returns>
        public string GetMaxLKWarehouseContractCode()
        {
            using (var conn = new DbContext())
            {
                return conn.Connection.QueryFirst<string>("SELECT MAX(CAST(SUBSTRING(ContractNo, 2,LEN(ContractNo)-1) AS int)) AS MaxContractNumber FROM LK_WarehouseContract WHERE ISNUMERIC(SUBSTRING(ContractNo, 2, LEN(ContractNo) - 1)) = 1");
            }
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public PagedList<LKWarehouseContract> GetLKWarehouseContractPageList(LKWarehouseContractInput input)
        {
            string sql = @"SELECT c.[Id]
      ,[ContractNo]
      ,[CustomerId]
      ,[CustomerName]
      ,[ContractTime]
      ,[BeginTime]
      ,[EndTime]
      ,[StoreType]
	  ,CASE c.StoreType
	  WHEN 1 THEN '包面积'
	  WHEN 2 THEN '包卡位'
	  ELSE '' END AS StoreTypeName
      ,[StoreAlgorithm]
	  ,CASE c.StoreAlgorithm
	  WHEN 1 THEN '按自然月计算'
	  WHEN 2 THEN '按计费月计算'
	  WHEN 3 THEN '按自然天计算'
	  ELSE '' END AS StoreAlgorithmName
      ,[UnitPrice]
      ,[Number]
      ,[Amount]
      ,[IsByTon]
      ,[WareHouseId],
	  w.WareHouseName
      ,[IsValid],c.CreateTime,c.Status,c.CompanyId
  FROM [dbo].[LK_WarehouseContract] AS c
  LEFT JOIN dbo.LK_WareHouse AS w ON c.WareHouseId=w.Id AND w.IsDeleted=0 WHERE c.IsDeleted=0 @WHERE";
            List<string> conditions = new List<string>();
            //判断input的属性是否为空，如果不为空则添加到conditions中

            if (!string.IsNullOrWhiteSpace(input.ContractNo))
            {
                input.ContractNo = $"%{input.ContractNo}%";
                conditions.Add("ContractNo LIKE @ContractNo");
            }
            if (input.BeginTime.HasValue)
            {
                conditions.Add("CreateTime >= @BeginTime");
            }
            if (input.EndTime.HasValue)
            {
                conditions.Add("CreateTime <= @EndTime");
            }
            if (input.CustomerId.HasValue)
            {
                conditions.Add("CustomerId = @CustomerId");
            }
            if (input.ModifyUserId.HasValue)
            {
                conditions.Add("ModifyUserId = @ModifyUserId");
            }
            if (input.IsValid.HasValue)
            {
                conditions.Add("IsValid = @IsValid");
            }

            if (UserContext.RoleId != (int)Roles.管理员)
            {
                conditions.Add($"c.CompanyId={UserContext.CompanyId}");
            }

            string condition = string.Empty;
            if (conditions.Any())
            {
                condition = $" AND {string.Join(" AND ", conditions)}";
            }
            sql = !string.IsNullOrWhiteSpace(condition) ? sql.Replace("@WHERE", condition) : sql.Replace("@WHERE", "");

            using (var conn = new DbContext())
            {
                return conn.Connection.GetPagedList<LKWarehouseContract>(sql, "CreateTime Desc", input);
            }
        }

        /// <summary>
        /// 通过客户id获取包仓合同
        /// </summary>
        /// <param name="customerId"></param>
        /// <returns></returns>
        public List<ContractDto> GetContractListByCustomerId(int customerId)
        {
            using (var conn = new DbContext())
            {
                //                string sql = @"SELECT ContractNo,1 AS ContractType,'零仓' AS ContractTypeName,ChargingUnit
                //,CASE [ChargingUnit]
                //WHEN 1 THEN '按吨'
                //WHEN 2 THEN '按件'
                //WHEN 3 THEN '按托盘/面积'
                //WHEN 4 THEN '按板'
                //ELSE '' END AS ChargingUnitName
                //,ColdUnitPrice
                //,ColdAlgorithm
                //,CASE ColdAlgorithm
                //WHEN 1 THEN '按周期收取'
                //WHEN 2 THEN '先按周期后按天收取'
                //WHEN 3 THEN '按每天收取'
                //WHEN 4 THEN '按合同收取'
                //WHEN 5 THEN '按天后收，月结'
                //WHEN 6 THEN '按天后收，不月结'
                //ELSE '' END AS ColdAlgorithmName
                //,Cycle FROM dbo.LK_ZeroWarehouseContract WHERE Status=0 AND IsDeleted=0 AND CustomerId=@customerId
                //UNION ALL
                //SELECT ContractNo,2 AS ContractType,'包仓' AS ContractTypeName,1,'按吨',UnitPrice,StoreAlgorithm AS ColdAlgorithm ,CASE StoreAlgorithm
                //  WHEN 1 THEN '按自然月计算'
                //  WHEN 2 THEN '按计费月计算'
                //  WHEN 3 THEN '按自然天计算'
                //  ELSE '' END AS ColdAlgorithmName,1  FROM LK_WarehouseContract WHERE Status=0 AND IsDeleted=0 AND CustomerId=@customerId";
                string sql = @"SELECT ContractNo,2 AS ContractType,'包仓' AS ContractTypeName,1 AS ChargingUnit,'按吨' AS ChargingUnitName,UnitPrice,StoreAlgorithm AS ColdAlgorithm ,CASE StoreAlgorithm
  WHEN 1 THEN '按自然月计算'
  WHEN 2 THEN '按计费月计算'
  WHEN 3 THEN '按自然天计算'
  ELSE '' END AS ColdAlgorithmName,1 AS Cycle,BeginTime,EndTime  FROM LK_WarehouseContract WHERE Status=0 AND IsDeleted=0 AND CustomerId=@customerId";
                return conn.Connection.Query<ContractDto>(sql, new { customerId }).ToList();
            }
        }

        /// <summary>
        /// 获取客户下零仓合同
        /// </summary>
        /// <param name="customerId"></param>
        /// <returns></returns>
        public List<ContractDto> GetZeroContractListByCustomerId()
        {
            using (var conn = new DbContext())
            {
                string sql = @"SELECT ContractNo,1 AS ContractType,'零仓' AS ContractTypeName,ChargingUnit
                ,CASE [ChargingUnit]
                WHEN 1 THEN '按吨'
                WHEN 2 THEN '按件'
                WHEN 3 THEN '按托盘/面积'
                WHEN 4 THEN '按板'
                ELSE '' END AS ChargingUnitName
                ,ColdUnitPrice
                ,ColdAlgorithm
                ,CASE ColdAlgorithm
                WHEN 1 THEN '按周期收取'
                WHEN 2 THEN '先按周期后按天收取'
                WHEN 3 THEN '按每天收取'
                WHEN 4 THEN '按合同收取'
                WHEN 5 THEN '按天后收，月结'
                WHEN 6 THEN '按天后收，不月结'
                ELSE '' END AS ColdAlgorithmName
                ,Cycle,Days,GETDATE() AS BeginTime,DATEADD(day, Days, GETDATE()) AS  EndTime FROM dbo.LK_ZeroWarehouseContract WHERE IsDeleted=0";
                return conn.Connection.Query<ContractDto>(sql).ToList();
            }
        }

        //删除包仓合同
        public bool DeleteLKWarehouseContract(int id)
        {
            return UseTransaction((conn, trans) =>
            {
                conn.Execute("UPDATE LK_WarehouseContract SET IsDeleted=1 WHERE Id=@id", new { id }, trans);
                conn.Execute("UPDATE LK_WarehouseContractDetail SET IsDeleted=1 WHERE ContractId=@id", new { id }, trans);
                conn.Execute("UPDATE LK_Incidentals SET IsDeleted=1 WHERE ContractId=@id AND ContractType=2", new { id }, trans);

                return true;
            });
        }

        /// <summary>
        /// 新增包仓合同
        /// </summary>
        /// <param name="contract"></param>
        /// <param name="costList"></param>
        /// <returns></returns>
        public bool AddLKWarehouseContract(LKWarehouseContract contract, List<LKIncidentals> costList, List<LKWarehouseContractDetail> detailList)
        {
            return UseTransaction((conn, trans) =>
            {
                var result = conn.Insert(contract, trans);

                if (costList != null && costList.Any())
                {
                    costList.ForEach(item => item.ContractId = contract.Id);
                    conn.Insert(costList, trans);
                }
                if(detailList != null && detailList.Any())
                {
                    detailList.ForEach(item => item.ContractId = contract.Id);
                    conn.Insert(detailList, trans);
                }

                return result > 0;
            });
        }

        //编辑包仓合同
        public bool EditLKWarehouseContract(LKWarehouseContract contract, List<LKIncidentals> costList, List<LKWarehouseContractDetail> detailList)
        {
            return UseTransaction((conn, trans) =>
            {
                conn.Update(contract, trans);

                if (costList != null && costList.Any())
                {
                    costList.ForEach(item => item.ContractNo = contract.ContractNo);
                    var adds = costList.Where(c => c.Id == 0).ToList();
                    var updates = costList.Where(c => c.Id > 0).ToList();

                    if (updates.Any())
                    {
                        conn.Update(updates, trans);

                        var upids = updates.Select(c => c.Id).ToList();
                        var deletes = conn.GetListByWhere<LKIncidentals>("ContractType=2 AND ContractId=@cid AND Id Not IN @ids", new { cid = contract.Id, ids = upids }, trans).Select(s => s.Id);
                        if (deletes.Any())
                        {
                            conn.DeleteByIds<LKIncidentals, int>(deletes.ToList(), trans);
                        }
                    }
                    if (adds.Any())
                    {
                        adds.ForEach(item =>
                        {
                            item.ContractId = contract.Id;
                            item.ContractNo = contract.ContractNo;
                            item.ContractType = (int)BillType.WarehouseContract;
                            item.CreateUser = LoginHelper.UserName();
                            item.CreateUserId = LoginHelper.GetUserId();
                        });
                        conn.Insert(adds, trans);
                    }
                }

                if (detailList != null && detailList.Any())
                {
                    var adds = detailList.Where(c => c.Id == 0).ToList();
                    var updates = detailList.Where(c => c.Id > 0).ToList();


                    if (updates.Any())
                    {
                        conn.Update(updates,trans);

                        var upids = updates.Select(s => s.Id);
                        var deletes = conn.GetListByWhere<LKWarehouseContractDetail>("ContractId=@cid AND Id Not IN @ids", new { cid = contract.Id, ids = upids }, trans).Select(s => s.Id);//lKWarehouseContractDetailDal.GetListByWhere("Id Not IN @ids", new { ids = upids }).Select(s => s.Id);
                        if (deletes.Any())
                        {
                            conn.DeleteByIds<LKWarehouseContractDetail, int>(deletes.ToList(), trans);
                        }
                    }
                    else 
                    {
                        conn.DeleteByWhere<LKWarehouseContractDetail>("ContractId=@cid", new { cid = contract.Id }, trans);
                    }
                    if (adds.Any())
                    {
                        adds.ForEach(item =>
                        {
                            item.ContractId = contract.Id;
                            item.CreateTime = DateTime.Now;
                        });
                        conn.Insert(adds, trans);
                    }

                }

                return true;
            });
        }
    }
}

